const knex = require('knex');
const path = require('path');
const fs = require('fs').promises;

let mainKnex = null;
const caseKnexInstances = new Map();
let projectRoot = null;

/**
 * Initialize main database connection
 */
function initMainKnex(rootPath) {
    if (mainKnex) return mainKnex;

    projectRoot = rootPath;
    const mainDbPath = path.join(rootPath, 'cases.db');

    mainKnex = knex({
        client: 'sqlite3',
        connection: { filename: mainDbPath },
        useNullAsDefault: true,
        pool: {
            min: 1,
            max: 10,
            afterCreate: (conn, cb) => {
                conn.run('PRAGMA foreign_keys = ON', cb);
            }
        }
    });

    return mainKnex;
}

/**
 * Get main database instance
 */
function getMainKnex() {
    if (!mainKnex) {
        throw new Error('Main database not initialized');
    }
    return mainKnex;
}

/**
 * Get or create case database connection
 */
function getCaseKnex(caseId, dbPath) {
    console.log(`[DB] getCaseKnex called for case ${caseId}, dbPath: ${dbPath}`); // Debug log

    if (caseKnexInstances.has(caseId)) {
        console.log(`[DB] Returning existing connection for case ${caseId}`); // Debug log
        return caseKnexInstances.get(caseId);
    }

    const absolutePath = path.isAbsolute(dbPath)
        ? dbPath
        : path.join(projectRoot, dbPath);

    console.log(`[DB] Creating new connection with absolute path: ${absolutePath}`); // Debug log

    const caseKnex = knex({
        client: 'sqlite3',
        connection: { filename: absolutePath },
        useNullAsDefault: true,
        pool: {
            min: 1,
            max: 5,
            afterCreate: (conn, cb) => {
                conn.run('PRAGMA foreign_keys = ON', cb);
            }
        }
    });

    caseKnexInstances.set(caseId, caseKnex);
    console.log(`[DB] Created and stored new connection for case ${caseId}`); // Debug log
    return caseKnex;
}

/**
 * Close case database connection
 */
async function closeCaseKnex(caseId) {
    if (caseKnexInstances.has(caseId)) {
        await caseKnexInstances.get(caseId).destroy();
        caseKnexInstances.delete(caseId);
    }
}

/**
 * Close all connections
 */
async function closeAllConnections() {
    for (const [caseId, instance] of caseKnexInstances) {
        await instance.destroy();
    }
    caseKnexInstances.clear();

    if (mainKnex) {
        await mainKnex.destroy();
        mainKnex = null;
    }
}

/**
 * Ensure global tables exist (cases, profiles, configs)
 */
async function ensureGlobalTables() {
    const knex = getMainKnex();

    // 1. Cases Table
    const casesExists = await knex.schema.hasTable('cases');
    if (!casesExists) {
        await knex.schema.createTable('cases', (table) => {
            table.increments('id').primary();
            table.string('case_name').notNullable();
            table.string('case_status').notNullable();
            table.string('case_number').notNullable().unique();
            table.timestamp('created_time').defaultTo(knex.fn.now());
            table.datetime('case_start_time');
            table.string('password');
            table.text('description');
            table.integer('closed_status').defaultTo(0);
            table.string('db_path');
        });
    }

    // Ensure columns exist for cases
    const columns = await knex('cases').columnInfo();
    if (!columns.closed_status) {
        await knex.schema.alterTable('cases', (table) => {
            table.integer('closed_status').defaultTo(0);
        });
    }
    if (!columns.db_path) {
        await knex.schema.alterTable('cases', (table) => {
            table.string('db_path');
        });
    }

    // 2. Analyzer Profiles Table
    const profilesExists = await knex.schema.hasTable('analyzer_profiles');
    if (!profilesExists) {
        await knex.schema.createTable('analyzer_profiles', (table) => {
            table.increments('id').primary();
            table.string('name').notNullable().unique();
            table.text('configs').notNullable(); // JSON string
            table.text('description');
            table.timestamp('updated_at').defaultTo(knex.fn.now());
        });
    }

    // 3. AI Prompt Templates Table
    const promptsExists = await knex.schema.hasTable('ai_prompt_templates');
    if (!promptsExists) {
        await knex.schema.createTable('ai_prompt_templates', (table) => {
            table.increments('id').primary();
            table.string('scene_key').notNullable().unique();
            table.text('template').notNullable();
            table.timestamp('updated_at').defaultTo(knex.fn.now());
        });
    }

    // 4. Global Configs Table
    const configsExists = await knex.schema.hasTable('global_configs');
    if (!configsExists) {
        await knex.schema.createTable('global_configs', (table) => {
            table.increments('id').primary();
            table.string('key').notNullable().unique();
            table.text('value').notNullable();
            table.timestamp('updated_at').defaultTo(knex.fn.now());
        });
    }
}

/**
 * Ensure case-specific tables exist (logs, profiles)
 */
async function ensureCaseTables(caseKnex) {
    // 1. Import Logs
    const logsExists = await caseKnex.schema.hasTable('import_logs');
    if (!logsExists) {
        await caseKnex.schema.createTable('import_logs', (table) => {
            table.increments('id').primary();
            table.integer('case_id');
            table.string('file_name');
            table.string('file_hash');
            table.string('data_type');
            table.timestamp('import_time').defaultTo(caseKnex.fn.now());
            table.unique(['case_id', 'file_hash']);
        });
    }

    // 2. Profile Entities (People/Companies) - Enhanced for comprehensive entity system
    const entitiesExists = await caseKnex.schema.hasTable('profile_entities');
    if (!entitiesExists) {
        await caseKnex.schema.createTable('profile_entities', (table) => {
            table.increments('id').primary();
            table.string('entity_type').defaultTo('person'); // 'person' | 'company'
            // Core identifiers
            table.string('id_card').index();       // 身份证号（个人核心标识）
            table.string('credit_code').index();   // 统一社会信用代码（企业核心标识）
            // Basic info
            table.string('name').index();          // 姓名/企业名称
            table.string('gender');                // 性别（个人）
            table.string('birth_date');            // 出生日期（个人）
            table.string('province');              // 籍贯/注册地
            table.string('photo_path');            // 照片路径
            // Extended info
            table.text('phones');                  // JSON: 关联手机号数组
            table.text('accounts');                // JSON: 关联银行账户数组
            table.text('addresses');               // JSON: 关联地址数组
            table.text('extra_data');              // JSON: 扩展数据
            // Source tracking
            table.string('source_table');          // 首次来源表
            table.integer('source_id');            // 首次来源记录ID
            // Statistics
            table.integer('risk_score').defaultTo(0);
            table.text('tags');                    // JSON: 标签数组
            // Timestamps
            table.timestamp('created_at').defaultTo(caseKnex.fn.now());
            table.timestamp('updated_at').defaultTo(caseKnex.fn.now());
        });
    } else {
        // Ensure new columns exist for existing tables
        const columns = await caseKnex('profile_entities').columnInfo();
        if (!columns.id_card) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.string('id_card').index();
            });
        }
        if (!columns.credit_code) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.string('credit_code').index();
            });
        }
        if (!columns.entity_type) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.string('entity_type').defaultTo('person');
            });
        }
        if (!columns.phones) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.text('phones');
            });
        }
        if (!columns.accounts) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.text('accounts');
            });
        }
        if (!columns.addresses) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.text('addresses');
            });
        }
        if (!columns.source_table) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.string('source_table');
            });
        }
        if (!columns.source_id) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.integer('source_id');
            });
        }
        if (!columns.gender) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.string('gender');
            });
        }
        if (!columns.birth_date) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.string('birth_date');
            });
        }
        if (!columns.province) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.string('province');
            });
        }
        if (!columns.photo_path) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.string('photo_path');
            });
        }
        if (!columns.extra_data) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.text('extra_data');
            });
        }
        if (!columns.created_at) {
            await caseKnex.schema.alterTable('profile_entities', (table) => {
                table.timestamp('created_at').defaultTo(caseKnex.fn.now());
            });
        }
    }

    // 3. Profile Relationships (Graph Edges)
    const relationsExists = await caseKnex.schema.hasTable('profile_relationships');
    if (!relationsExists) {
        await caseKnex.schema.createTable('profile_relationships', (table) => {
            table.string('source_identifier');
            table.string('target_name');
            table.string('target_identifier');
            table.string('relation_type'); // bank, call, travel
            table.integer('weight').defaultTo(0); // count or amount
            table.text('details'); // JSON summary
            table.unique(['source_identifier', 'target_identifier', 'relation_type']);
        });
    }

    // 4. Profile Timeline (Activity Log)
    const timelineExists = await caseKnex.schema.hasTable('profile_timeline');
    if (!timelineExists) {
        await caseKnex.schema.createTable('profile_timeline', (table) => {
            table.string('identifier').index();
            table.datetime('event_time').index();
            table.string('event_type'); // bank, call, travel
            table.string('event_summary');
            table.string('location');
            table.string('source_table');
            table.integer('source_id');
        });
    }

    // 5. Profile Data Links (Links profiles to source data records)
    const linksExists = await caseKnex.schema.hasTable('profile_data_links');
    if (!linksExists) {
        await caseKnex.schema.createTable('profile_data_links', (table) => {
            table.increments('id').primary();
            table.integer('profile_id').notNullable().index();
            table.string('source_table').notNullable();  // 来源表名
            table.integer('source_id').notNullable();    // 来源记录ID
            table.string('role');                        // 角色: 'owner' | 'counterparty' | 'caller' | 'callee' | 'guest'
            table.timestamp('linked_at').defaultTo(caseKnex.fn.now());
            table.unique(['profile_id', 'source_table', 'source_id', 'role']);
        });
    }

    // 6. Personal Info Table (用于导入公安个人信息/廉政档案等)
    const personalInfoExists = await caseKnex.schema.hasTable('personal_info');
    if (!personalInfoExists) {
        await caseKnex.schema.createTable('personal_info', (table) => {
            table.increments('id').primary();
            table.string('name').index();             // 姓名
            table.string('id_card').index();          // 身份证号
            table.string('gender');                   // 性别
            table.string('birth_date');               // 出生日期
            table.string('ethnic');                   // 民族
            table.string('address');                  // 户籍地址
            table.string('phone');                    // 联系电话
            table.string('workplace');                // 工作单位
            table.string('position');                 // 职务
            table.string('education');                // 学历
            table.string('political_status');         // 政治面貌
            table.string('marital_status');           // 婚姻状况
            table.string('occupation');               // 职业
            table.string('province');                 // 户籍地区划
            table.string('native_place');             // 籍贯
            table.string('other_address');            // 其他地址
            table.string('blood_type');               // 血型
            table.string('former_name');              // 曾用名
            table.string('alias');                    // 别名/绰号
            table.string('height');                   // 身高
            table.string('appearance');               // 体貌特征
            table.string('military_status');          // 兵役状况
            table.string('zodiac');                   // 星座
            table.string('chinese_zodiac');           // 生肖
            table.string('behavior_tags');            // 行为标签
            table.text('extra_data');                 // JSON扩展
            table.string('row_hash');                 // 数据指纹
            table.timestamp('created_at').defaultTo(caseKnex.fn.now());
        });
    }

    // 7. FTS5 Global Search Index
    try {
        const ftsExists = await caseKnex.raw("SELECT name FROM sqlite_master WHERE type='table' AND name='fts_data_index'");
        if (ftsExists.length === 0) {
            // Create FTS5 virtual table
            // content: indexed text
            // title: display title
            // original_table: source table name
            // original_id: source record id
            // data_type: type of data (bank, call, etc)
            await caseKnex.raw(`
                CREATE VIRTUAL TABLE fts_data_index USING fts5(
                    content,
                    title,
                    original_table UNINDEXED,
                    original_id UNINDEXED,
                    data_type UNINDEXED
                )
            `);
            console.log('[DB] Created fts_data_index virtual table');
        }
    } catch (err) {
        console.warn('[DB] Failed to create FTS5 table, FTS might not be supported:', err.message);
    }
}

/**
 * Get case database info and connection
 */
async function getCaseDatabaseInfo(caseId) {
    // 1. Try to get from memory cache first
    if (caseKnexInstances.has(caseId)) {
        const instance = caseKnexInstances.get(caseId);
        const filename = instance.client.connectionSettings.filename;
        console.log(`[DB] Found case ${caseId} in memory cache, dbPath: ${filename}`);
        return {
            knex: instance,
            dbPath: filename,
            caseNumber: null // Might be missing, but usually not critical for just getting connection
        };
    }

    console.log(`[DB] Attempting to get database info for case ID: ${caseId} (Type: ${typeof caseId})`);
    const knex = getMainKnex();

    const caseInfo = await knex('cases')
        .select('db_path', 'case_number')
        .where('id', caseId)
        .first();

    console.log(`[DB] Retrieved case info:`, caseInfo);

    if (!caseInfo) {
        // Debug: List all cases to see what's wrong
        const allCases = await knex('cases').select('id', 'case_name');
        console.log('[DB] All available cases:', allCases);
        throw new Error(`案件不存在 (ID: ${caseId})`);
    }

    let dbPath = caseInfo.db_path;

    // Create database if not exists
    if (!dbPath) {
        console.log(`[DB] No db_path found for case ${caseId}, creating new path`); // Debug log

        // Get storage path from config
        let storageRoot = projectRoot; // fallback
        try {
            const config = await knex('global_configs').where('key', 'storage_path').first();
            if (config && config.value) {
                storageRoot = config.value;
                console.log(`[DB] Using configured storage path: ${storageRoot}`);
            } else {
                console.log(`[DB] No storage path configured, using default: ${storageRoot}`);
            }
        } catch (err) {
            console.log(`[DB] Failed to read storage config, using default: ${storageRoot}`, err);
        }

        const casesDir = path.join(storageRoot, 'cases');
        await fs.mkdir(casesDir, { recursive: true });
        dbPath = path.join(casesDir, `${caseInfo.case_number}_id${caseId}.db`);

        await knex('cases').where('id', caseId).update({ db_path: dbPath });
        console.log(`[DB] Created new db path: ${dbPath}`); // Debug log
    }

    console.log(`[DB] Final dbPath: ${dbPath}`); // Debug log

    // Verify file exists
    try {
        await fs.access(dbPath);
        console.log(`[DB] Database file exists: ${dbPath}`); // Debug log
    } catch (e) {
        console.log(`[DB] Database file does not exist, creating: ${dbPath}`, e); // Debug log
        // Create the file
        const caseKnex = getCaseKnex(caseId, dbPath);
        await ensureCaseTables(caseKnex);
        console.log(`[DB] Created new database file with tables: ${dbPath}`); // Debug log
    }

    console.log(`[DB] Returning connection for ${dbPath}`); // Debug log

    // Try to create the connection and check if it's valid
    try {
        const caseKnex = getCaseKnex(caseId, dbPath);
        // Test the connection by running a simple query
        await caseKnex.raw('SELECT 1 as test');
        console.log(`[DB] Connection test successful for: ${dbPath}`); // Debug log
        return {
            knex: caseKnex,
            dbPath,
            caseNumber: caseInfo.case_number
        };
    } catch (error) {
        console.error(`[DB] Failed to create database connection for: ${dbPath}`, error); // Debug log
        throw error;
    }
}

module.exports = {
    initMainKnex,
    getMainKnex,
    getCaseKnex,
    closeCaseKnex,
    closeAllConnections,
    ensureGlobalTables,
    ensureCaseTables,
    getCaseDatabaseInfo
};
